package main

import (
	"database/sql"

	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

func main() {

	db, err := sql.Open("mysql", "root:0314@tcp(127.0.0.1:3306)/golang_test?charset=utf8")

	checkErr(err)

	defer db.Close()

	// insert(db)

	// query(db)

	// update(db)

	// delete(db)

}

// insert

func insert(db *sql.DB) {

	stmt, err := db.Prepare("INSERT user (name, age) values (?,?)")

	checkErr(err)

	res, err := stmt.Exec("Eric", 22)

	checkErr(err)

	id, err := res.LastInsertId()

	checkErr(err)

	fmt.Printf("insert id=%d\n", id)

}

// query

func query(db *sql.DB) {

	rows, err := db.Query("SELECT * FROM user")

	checkErr(err)

	// 列，字段名

	columns, err := rows.Columns()

	checkErr(err)

	scanArgs := make([]interface{}, len(columns))

	values := make([]interface{}, len(columns))

	for i := range values {

		scanArgs[i] = &values[i]

	}

	for rows.Next() {

		err = rows.Scan(scanArgs...)

		checkErr(err)

		// 行，记录

		record := make(map[string]string)

		for i, col := range values {

			if col != nil {

				record[columns[i]] = string(col.([]byte))

			}

			fmt.Println(record)

		}

	}

}

// update

func update(db *sql.DB) {

	stmt, err := db.Prepare("UPDATE user SET name=?,age=? WHERE id=?")

	checkErr(err)

	res, err := stmt.Exec("Rose", 21, 1)

	checkErr(err)

	num, err := res.RowsAffected()

	fmt.Printf("affected：%d\n", num)

}

// delete

func delete(db *sql.DB) {

	stmt, err := db.Prepare("DELETE FROM user WHERE id=?")

	checkErr(err)

	res, err := stmt.Exec(1)

	checkErr(err)

	num, err := res.RowsAffected()

	fmt.Printf("affected：%d\n", num)

}

func checkErr(err error) {

	if err != nil {

		panic(err)

	}

}
